package com.yyw.bug;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yyw.bug.common.FileUtil;

/**
 * Excel表格导入
 * 
 * @author frank
 * 
 * 注意：数值需在excel中设置成数值格式，在此仅能识别String类型和double类型
 */
public class WorkExcel {
	
	private static final String fileFrom = "D:/wechat.xlsx";//excel文件
	private static final String fileTo = "D:/frank.txt";//生成目标文件
	
	public static void main(String[] args) throws FileNotFoundException, IOException {
		File file = new File(fileFrom);
		Workbook wookbook = null;
		try {
			wookbook = new XSSFWorkbook(new FileInputStream(file));// excel
		} catch (Exception ex) {
			wookbook = new HSSFWorkbook(new FileInputStream(file));// poi
		}
		Sheet sheet = wookbook.getSheetAt(0);
		int rows = sheet.getPhysicalNumberOfRows();

		// 第1行开始为数据部分
		int index = 0;
		int i = 0;
		String SQL = "";
		while (index < rows) {
			Row row = sheet.getRow(i++);
			if (row == null) {
				continue;
			}
			index++;
			String wechatOrder = getCell(row, 0);// 微信订单
			String yywOrder = getCell(row, 1);// 订单
			String amount = getCell(row, 2);// 金额

			//SQL 具体情况而定
			String upteteUSql = "UPDATE ecord_shoporder SET PayStatus =1,HadPay ="
					+ amount
					+ ",NetMode=46,BankCode=605,OutPayNumber = '"
					+ wechatOrder
					+ "',PayTime = now() WHERE OrderId = '"
					+ yywOrder + "';\n";
			String insertSql = "INSERT INTO ecord_ordernetpayrecord(OrderId,PayType,PayBank,HadPay, PayTime,OutPayNumber,BankCode,Status, AddTime) VALUES('"
					+ yywOrder
					+ "',1,46,"
					+ amount
					+ ", now(),'"
					+ wechatOrder
					+ "',605,0,now());\n\n";
			SQL += upteteUSql + insertSql;
		}
		
		FileUtil.writeFile(fileTo, SQL);
	}

	/**
	 * 获取每一格的值
	 * 
	 * @param row
	 * @param num
	 * @return
	 */
	private static String getCell(Row row, int num) {
		Cell cell = row.getCell(num);
		try {
			if (cell != null) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				return cell.getStringCellValue().trim();
			}
		} catch (Exception e) {
			return String.valueOf(cell.getNumericCellValue()).trim();
		}
		return "";
	}

}
